Risc World 3

home *** CD-ROM | disk | FTP | other *** search

/ Risc World 3 / Risc World 3.iso / SOFTWARE / ISSUE4 / POWERBASE / DOCS / Ch04-Creat < prev next >

Wrap

Text File | 2001-01-31 | 40.2 KB | 789 lines

============================== Ch 4 − Creating a New Database ============================== Five steps are involved in setting up a new database: (1) Create the database application shell. (2) Design the record layout. (3) Specify the number of records the database is to contain. (4) Specify the primary key. (5) Build the empty database. 4.1 Creating the database application shell ----------------------------------------------- You need to have Powerbase installed on the iconbar but with no database open, i.e. “No data” should appear under the icon. Click SELECT over the icon and a Save box will appear containing the default name !Database. Type in the name of your database and drag the icon to a directory window. Remember that, for RISC OS versions prior to 4.00, the name cannot exceed 10 characters, including the initial “!”. If you enter more the name will be truncated and you could end up overwriting an existing database with a similar name. You don’t actually need to enter the “!”; Powerbase will insert it automatically. If you open the newly-created application directory (Shift double-click) you will find that it contains four files (!Run, !Sprites, !Sprites22 and Data) and five directories (Indices, Menus, PrintJobs, PrintRes and ValTables). All Powerbase databases expect these objects to be present so don’t delete any of them. 4.2 Designing the record layout ----------------------------------- When you created the application shell you were left with a window on screen, blank apart from a grid of blue lines, and it is here that you must design the database record. This is the lengthiest part of setting up a database, although efforts have been made to render it as easy as possible. If you have closed the window just click SELECT on the Powerbase icon on the iconbar to re-open it. 4.2.1 Simple field creation Clicking MENU over the window brings up the New database menu on which every item except Create field and Grid is shaded at this stage. Choosing Create field displays the Field Definition window which lets you specify the characteristics of a field. Grid allows you to choose options for the grid used to lay out the fields. This grid appears only at the design stage, not on a working database (except when using the Adjust format and New record format features). It may be turned off but you will probably find it a help in getting the field layout as you want it. There are options to choose the colour of the grid, whether it is represented by solid or dotted lines and what the spacing between the lines is. By default the grid has solid, light blue lines spaced 32 OS units apart and fields will “snap” to it at intervals of 4 OS units. The snap interval may be altered or the snapping disabled. First decide what class of field you want to create. There are eight such classes, selected via radio buttons: (1) Editable (5) Computed (2) Scrollable list (6) Stamp (3) Check-box (7) Keypad button (4) External (8) Extra button For the present we will confine ourselves to the first, which is the default selection. As well as the field class you must decide on the type of field within the class. The default offered is Unrestricted, meaning that it will accept all printable characters. We’ll look at other types later. Decide on a name for the field and enter it in the Descriptor icon. This is the name which will appear on the record window and may be up to 40 characters long. You must also enter a Tag, which is used to identify the field in search formulae and is limited to 4 characters. Next enter the Data length; the maximum number of characters the field is to hold. Values up to 246 are allowed. Now click on Create and the field will appear on the record window. It’s probably not where you want it so use SELECT to drag the white rectangle to the position required. When you drop the field in its new position the descriptor falls into place too. If you want the descriptor somewhere other than to the left of the data icon (above it, for example) move it by itself after positioning the data icon. For fine adjustment re-open the Create window by double-clicking on the field and nudge the field into position using the bump icons at the bottom left corner. - 27 - To edit an existing field you can either click MENU over the field and choose Edit field or simply double-click SELECT over the field itself as above. You can also display the data for any field by clicking MENU anywhere in the window and choosing the field from the Fields Created submenu or by clicking the List fields button on the open dialogue box. After making changes click on Update (Create will be shaded). 4.2.2 Deleting, inserting and re-ordering fields. It is important to understand the difference between the physical position of fields on the screen and the internal numbering of the fields. The former is purely a matter of appearance and you may drag the fields about the screen to your heart’s content, but the latter is fundamental to the way the database will function. Each field has a number which corresponds to the order in which it was created. Whenever you invoke the Field Definition window the title-bar shows either the number of the (new) field you are about to create (e.g. “New field 3”) or the number of the (existing) field you were pointing at when you opened the menu (e.g. “Modify field 5”). The numbering of fields determines the order in which they will be “visited” by the caret when you are actually using the database and typing Return to get from field to field, i.e. the editing order. A field may be deleted by bringing it up for editing as described above and clicking on Remove field. Fields which come after the deleted field will then be found to have had their field-numbers reduced by 1. Inserting a field into the middle of the existing field-sequence is obviously a little more complicated because we have to specify where in the sequence the new field goes. You need to know the number of the field which you want to follow the new one. This number is entered in the before icon before you click on Create. The layout of this part of the window should make things clear: “Create before <n>”. If you examine fields which come after the insertion you will see that their numbers have increased by 1. You can change the numbering of a field by removing it and then re-inserting it, but there is a better way. Bring the field up for editing as previously described. We will call this the “current field”. Enter the number of another existing field in the same place as was used above to specify the insertion position of a new field. We’ll call this the “entered field”. You may then do one of the following: • Click Swap with. This does exactly what you would expect. It swaps the positions of the current field and the entered field in the field sequence (but not their placement on-screen). • Click Renumber as. This is a bit more complicated. The current field acquires the number of the entered field. If this involves giving the current field a lower number than previously the fields beyond the new position are all moved up one place to open a gap for it, at the same time closing up the gap left by moving the current field from its old position. If it is being given a higher number the fields above its old position all move down one place, closing up the gap left by its removal and opening a gap in the required place farther up the sequence. (Once again, the physical position of the fields on the window is unchanged.) 4.2.3 Moving and re-sizing the bounding box Left to itself Powerbase makes all its field icons the same height and of a suitable length to contain the number of characters specified in Data length. This may not be quite what you want. If the data length is large the field could run off the right edge of the window. You might also want to make the field taller for emphasis. (But not for displaying multiple lines. Only fields of Text block type can be multi-line. These are discussed later.) We have already seen how you can alter the position of a field by dragging with SELECT. By dragging at an edge with ADJUST you can change the size of the bounding box. You can also specify both size and position by entering the required width and height of the bounding-box in the W and H icons and the co-ordinates of the lower-left corner in the X and Y icons of the Field creation window. All these values are in OS units (the same units as are used for plotting to the screen) but the origin is the top left corner of the record window. This means that the Y values are always negative. Clicking on Fit automatically sets the bounding-box width to fit the data length. 4.2.4 More about tags and descriptors Tags are very important in Powerbase. They are used when querying the database to produce reports, export CSV files etc. and also by some internal operations. Every printable field (see 3.3) must have a tag and no two tags can be the same. Descriptors are less important. They are there to provide visible labels for fields and in some cases you may not need one at all. e.g. You might want the record to look like this: - 28 - NAME Fred Bloggs ADDRESS 27, Every Street Anytown Woolshire WL4 7XZ There are 5 fields here but, having given the second one the descriptor ADDRESS, you don’t really want descriptors for the remaining 3 (except perhaps POSTCODE for the last). It is quite in order to have null descriptors like this, but your must give each field a tag. Suitable ones might be NAME, ADD1, ADD2, ADD3, CODE. (Remember each must be unique and not more than 4 characters.) You can omit the tag where the Data length is 0. This allows you to create fields which are simply explanatory labels. Since there is no data in them there would be no reason to include them in a query. 4.2.5 Other types of Editable field So far we have only used fields of Unrestricted type. Clicking on bump icons to the left of the field type, or on the menu button to the right, lets you cycle through the various types available. These are: (a) Unrestricted Accepts any printable character. (b) Alphanumeric Accepts all letters and numerals and common punctuation. (c) Upper case Accepts capital letters and numerals only. (d) Numeric Accepts numerals, +, − and . (decimal point). (e) Yes/No/Maybe Accepts Y, N and ? only. (f) Date Accepts dates in the form dd-mm-yy or dd-mm-yyyy, checking that the date is valid and reporting an error otherwise. (g) Time Accepts times in the form hh:mm:ss up to a value of 23:59:59. The time is checked for validity and errors are reported. (h) Internet Special field for email addresses and web URLs. There is no restriction on character input, but double-clicking will call up your mail program or browser (if it has been “seen” by the filer) with the address loaded. When Numeric is selected, certain icons in the dialogue box which are normally shaded become available. Thus, you can specify floating-point, fixed-point or integer format by means of a group of radio buttons. You may also specify a maximum and minimum value for numbers which may be entered in these fields. The Numeric min icon is also used to hold the starting value for Sequence number fields (see 4.2.10). A Date field should be either 8 or 10 characters long in order to hold the date in one of the two formats specified above. The hyphen separator in these dates may be changed via the Preferences window (see 14.5.1). Powerbase is very tolerant of the way you actually enter a date. You may type any non-numeric character as a separator: Powerbase will make sense of an entry such as 4/5/87, converting it to 04-05-87 when you type Return. Arithmetic may be performed on dates held in this type of field, e.g. you could have a Calculated field subtract the contents of two Date fields and display the difference in days (see 6.1.5). Time fields also allow flexibility in how you enter the values. If you enter 3.45;9 it will be reformatted as 03:45:09. The colon separator may be changed via the Preferences window (see 14.5.1). You may also enter incomplete times which are, by default, interpreted as follows. A number entered on its own is treated as hours. Thus, if you enter 6 and type Return it will be reformatted as 06:00:00. Two number separated by a non-numeric character are treated as hours and minutes, e.g. 6/5 would be reformatted as 06:05:00. You may edit Powerbase’s Config file to reverse this behaviour so that 6 is formatted as 00:00:06 and 6/5 as 00:05:06. When using this mode of entry you may also suppress the hours part of the display for values less than an hour, e.g. so that 6/5 is formatted as 05:06. (See 14.6 for editing Config.) Like Date fields, Time fields may be included in calculations, e.g. to obtain the difference in seconds between two times or to average a number of times (see 6.1.4). 4.2.6 Scrollable lists These are also user-editable but are treated separately because they are like no other editable field. They are intended for record structures which contain closely-related data whose number might vary widely from one record to another. A music CD, for example, might have only 3 or 4 tracks but it could have 30 or more. If you are cataloguing CDs and including track information you don’t really want to define 30 separate fields to allow for just a few extreme records − and find even then that the odd disc has more than 30 tracks! A scrollable list lets you make allowance for a modest number of items, adding new ones as required for individual records. Since - 29 - the list is scrollable it occupies no more space on the record window no matter how many items are in the list. You may create this type of field with anywhere from 1-4 columns. The value entered in Data length is the column width, i.e. the number of characters which each cell can hold. All columns are normally of the same width (but see below for how to specify differing widths). When you click Create all that appears is a plain white rectangle: not until you complete the process of creating the database does it appear with the cells and vertical scroll-bar characteristic of this type of field. The height of this rectangle will determine the height, and hence the number of visible rows, in the list. By default it will accommodate 3 rows but you may drag with ADJUST to make it higher. When you release the mouse button the rectangle will snap to a height which exactly fits the nearest whole number of rows. As noted above, you can make the columns differ in width. The field is created as above and the other steps to produce a working database are carried out. You then need to create a little text file called Format containing the individual column widths, one to a line. There must be as many numbers as columns and the sum of the new column widths must be exactly the same as it was before. Suppose you had created a 3-column scrollable list with a Data length of 10, giving 3 columns, each capable of holding 10 characters. This means you have 30 characters to play with. You can put into Format the numbers 5, 15, 10 or 3, 7, 20 or any other 3 numbers which add up to 30. Where do you put the Format file? Open the database directory (Shift-double-click) and look for a subdirectory called Scroll<n>, where n is the field number of the scrollable list. Format should go in this subdirectory. The data contained in scrollable lists is not held in the Database file. Data for each record is in a separate file stored in a special system of subdirectories inside the database directory. In this they resemble External fields (see 4.2.8) 4.2.7 Check-box fields These are fields whose status changes when clicked on with SELECT. Five types are defined: (a) Cross/tick Displays a cross initially. A click changes it to a tick. A second click changes it back to a cross. (b) Null/tick Similar to (a), but initial state is an empty box. (c) Null/star Similar to (b), but second state is a star. (d) Null/tick/cross This is a three state check box the first state being an empty box. Repeated clicking cycles through tick, cross and back to empty box. (e) ?/tick/cross Another three-state check-box where the first is a question mark. Check-boxes provide the fastest way of entering true/false or yes/no type data and the 3-state types allow for yes/no/undecided situations. If you examine the ValStrings file inside !Powerbase.Resources you will find strings associated with each of these five types the latter parts of which read, respectively: QNo,Yes Q-,Yes Q-,* QNo,Yes,- QNo,Yes,? These specify what will actually appear in a print-out when a check-box field is included in a query (see 3.3). You may change them if you wish, but don’t omit the initial Q (although this will not appear in the print-out) and take care not to alter other parts of the string. 4.2.8 External fields External fields allow you to link Powerbase records to pieces of data of a size and type which make them unsuitable for inclusion in an Editable field. Such items are sometimes called “BLOBs” (Binary Large Objects) in the PC world. The field types in this class and the types of data linked to them are as follows: (a) Text Plain text files, such as Edit creates. (b) Sprite Sprite files, such as Paint creates. (c) Draw Drawings such as Draw creates. (d) Text block Plain text files, as for (a). (e) Picture Sprite files, as for (b). (f) Remote Anything! When you create a field of type Text, Sprite or Draw it appears on the record window as a button bearing a small version of the icon for Edit, Paint or Draw respectively. Files of the appropriate type may be dropped on - 30 - these buttons, whereupon the file is copied into a special system of subdirectories within the database application. Unlike fields of the Editable class the data doesn’t become part of the Database file within the application (see also 4.2.6); the Text, Sprite or Draw file retains its identity and may be exported for editing in the appropriate application. Clicking on the button in the record window will display the file if the filer knows the whereabouts of the relevant editor (Edit, Paint or Draw). Text block and Picture fields take things a step further by actually displaying a text or sprite file on the record window. The bounding box of the icon needs to be of suitable size to hold the text or sprite. In the case of a Text block too small a box will cause the text to appear truncated. None is actually lost; it just can’t all be displayed. Too small a box for a Picture field will cause the sprite to spread beyond its boundaries. (N.B. To display the same sprite on each record, e.g. a company logo, define the field as of type Logo, not Picture.) the contents of a Text block or Picture field can be loaded into Edit or Paint by double-clicking with SELECT. When the edited text or sprite is saved the Powerbase field will be seen to update. Remote fields allow any type of filer object (file, directory or application) to be linked to a button on the record window. They differ from Text, Sprite, and Draw buttons in that the linked object is not copied into the database directory; only its pathname is stored, hence the term “Remote”. This has both advantages and disadvantages. Because no copy is made disc space is saved and the database stays a manageable size; an important factor where large sprites are involved. On the other hand, if you transfer the database to another computer all references to the linked objects become invalid and might even cause retrieval of files which are quite different from the ones you originally attached to the records! Similarly, if you move a linked object to some other part of your hard disc the pathname stored by Powerbase will no longer be correct. A Remote button normally shows a large, down-pointing arrow inviting the user to drop an object onto it. It will accept files of any type, ordinary directories or applications. Once a link is established the button shows the appropriate file icon or a blue folder or the default application icon. A single click has the same effect as double-clicking the object in a filer window: directories are opened, applications are run, files are loaded into their applications, Obey files and Basic programs are executed. If the linked object can’t be found Powerbase will be aware that something used to be there and the button will display a question mark. If you wish to break the link between button and file object click MENU over the button, go to the Field submenu and choose Unlink object. The button will revert to its original down-pointing arrow icon. The uses of this field type are limitless. You can use it to catalogue your collection of GIFs, JPEGs, sound samples, Sibelius files, Impression documents or whatever. You could even use it as an application launcher, although I’m sure there are more convenient ones available! The full pathname of a Remote object is stored in the database. Since RISC OS allows pathnames up to 255 characters long they can occupy rather a lot of space in a large database. Powerbase does allow you to specify a maximum pathlength of less than 255 characters but you must be very careful when using it. The value is set in the !Powerbase.Resources.Config file and is called PathLen. When an empty database is created it will use whatever value of PathLen is currently configured and it may not subsequently be changed without a complete reformat of the database. Be wary, therefore, about reducing the default value of 255 characters. Since, however, it is now possible for databases to have Config files of their own it would be far better to place any lower value of PathLen in such a file and leave Powerbase’s default at 255. Any attempt to store an overlong pathname in a Remote field will be rejected with an error message. 4.2.9 Computed fields Discussion of Computed fields is postponed to Ch 6 − Performing Calculations. 4.2.10 Stamp fields Stamp fields resemble Computed fields in that you cannot edit them; Powerbase “stamps” the fields with the appropriate contents automatically. The following types are available: (a) Record number Database record number. (b) Sequence number Unique value numbered upwards from base value set by user. (c) Time Time at which record was created. (d) Date Date on which record was created. (e) Date and time Date and time of record creation. (f) Day Day on which record was created (in the form Sun, Mon etc or 1,2 etc. or day of month as 10, 24 etc). - 31 - (g) Month Month in which record was created (as either a string; Jan, Feb etc or a number; 1, 2 etc). (h) Year Year when record was created as four-digit number, e.g. 1993 (i) Last altered Records the date and time record is first created and updates it only if the record is subsequently altered. Merely displaying the record does not cause updating. (j) Logo Allows a sprite to be included as a logo on every record. These fields are stamped by Powerbase when a record is first entered and thereafter, with the exception of (i), stay fixed. (But see Ch.6 for similar types of field which auto-update.) Note the following: • When defining Record or Sequence number fields make sure the Data length icon contains a large enough value to accommodate the longest number which will be encountered. For the other types the required field length is already known by Powerbase and the Data length box is therefore shaded. • The base value from which sequence numbers begin is entered in the Numeric min box. When a record containing such a field is deleted the sequence number is not normally re-used; a new record is given a new sequence number. This, of course, leads to gaps in the numeric sequence and you might want to reassign the numbers so that the sequence is continuous. You can do so by means of Compact sequence from the Field submenu. Before this can be used the sequence number field must be indexed and selected as the current index. • Date fields may display the date in any of three formats: (1) Sun,01 Aug 1993 (called “Long date”) (2) 01-08-93 (called “Date 8”) (3) 01-08-1993 (called “Date 10”) (2) and (3) are identical to the formats in which Powerbase displays dates of the ordinary Editable type (see 4.2.5). The numbers refer to the field length occupied by the date stamp. • Logo fields require the name of the sprite to be entered as the tag of the field to be used as a logo. This means that the sprite name is limited to four characters. The icon for a Logo field (like that of a Picture field) must be large enough to hold the intended sprite. You may have several logo fields on your record, all the required sprites being included in a sprite file called UsrSprites which is placed inside the database directory. 4.2.11 Button fields Any or all of the control buttons on the Powerbase keypad may be made to appear on the record window itself. They have exactly the same functions as the keypad equivalents. These button fields let you build a customised database which allows the user to use only the features you want him/her to have access to since the keypad and menus can then be suppressed (see 11.2). There is also a group of Extra buttons which have no keypad equivalent: Print brings up the Match window for report printing. Print button (on screen) Exit duplicates the action of Close database on the iconbar menu. Exit button Quit duplicates the action of Quit on the iconbar menu. Quit button Run file button, unlinking Run file. This type of button displays an icon made up of four different filetype icons. When you drop a file onto the button the icon changes to that of the relevant file and subsequent clicks on the button will run the file. Text files are treated as Powerbase scripts (see Ch 12), other types of file (e.g. Obey files) have their normal Run action. It is important to appreciate the difference between this type of button and a Remote field button (see 4.2.8). The file associated with a Remote field belongs to a specific record and every record can have a different file. The file attached to a Run file button is the same whatever record is being displayed and merely provides a convenient means of executing the file. To break the link with the file click MENU over the button, go to the Field submenu and choose Unlink file. The name of the file, if required, may be given in the descriptor or the leafname may be made to appear on the button by editing the Config file. - 32 - Directory. This resembles the previous type. In its initial state the button displays the small directory icon. Dropping a directory onto it changes this to a large directory icon and clicking on the button opens a filer window on the directory. To break the link with the directory click MENU over the button, go to the Field submenu and choose Unlink directory. Options in the Config file allow you to determine what the filer window looks like; the default being small icons and alphabetic sorting. The leafname may be displayed on the button. Without it you could confuse Directory and Run file buttons with Remote fields. Menu. This button lets you associate a data field with a pop-up menu. Clicking the button and choosing a character string from the menu enters it into the data field. The field number of the data field is entered as the tag of the menu button. This happens automatically if you define the button immediately after the data field. The menu data is in a text file whose name is the tag of the associated data field plus the word “Menu”. Put the menu title on the first line of the file and each menu choice on a separate line. All user-menu files are stored in the Menus subdirectory inside the database directory. If you click on a Menu button without having constructed the relevant menu file you will be given appropriate instructions. 4.2.12 Mandatory fields This isn’t yet another class of fields. A mandatory field is one which must be filled in before you can access another record or close the database. It has already been noted that at least one of the primary key fields must not be blank (see 2.4.1), but any Editable field (but not Scrollable list) can be made mandatory by selecting the Must not be blank option button when designing the field. Such fields appear on a working database with red as the foreground colour. (This may be altered via the Colours window; see 14.5) 4.3 A short-cut to a working database ----------------------------------------- The third item on the New database menu is called Default database. Choosing this is by far the quickest way of getting a database up and running. Its action is to create three files inside the application directory. These are called Form (which holds the record design), PrimaryKey and Database (which will ultimately contain the entered records). The number of records in the database is set to 100, with 25 as the amount by which this should increase when the database becomes full. The primary key is defined as the first four characters of the first Editable field. The database is opened and a blank record displayed ready for data entry. Since you can always alter such things as the database size and primary key structure later, you might wish to use these defaults while you experiment with the database. If you want to choose the database size and primary key structure yourself follow the procedure in Sections 4.4 and 4.5 4.4 Specifying the database size ------------------------------------ This involves two steps: (1) Save the Form file (which contains all the field data). The Save form file menu entry leads to a standard Save box but, since the pathname is correctly set for saving the file inside your database application, all you actually need do is click on the menu item itself. (2) You will now see that the Database size choice is no longer shaded and may be used to reach the Size window in which you specify the number of records in the database and the increment for expanding the database when it becomes full. 4.5 Specifying the primary key ---------------------------------- 4.5.1 General procedure Click MENU and choose Primary key. This opens the Key Structure window. The primary key (or any other key) is derived from one or more record fields called key fields. Up to four key fields may be used to define a key but we will begin by using just one. Four pieces of information need to be specified: (1) Choose the field, either by clicking on the bump icons or choosing from the associated pop-up menu. The default is the first Editable field in the record. (2) Enter the word within the field from which characters are to be taken to make up the key. The default is word 1. If 0 is entered word boundaries are ignored (see 4.5.2). (3) Enter the position within the word from which characters are to be taken. L (default) means from start of word, R means take from end. A number (n) means start at the nth character. - 33 - (4) Enter the number of characters to be taken from the word. The initially-set value is 4 but thereafter the default is the defined field length. After entering this information click on Create and the empty Database and PrimaryKey files will be created, after which the database will open ready for you to start entering records. 4.5.2 Some illustrative examples Key fields should be chosen with care. An ideal key field is one whose contents would never be repeated in another record. Powerbase lets you enforce this condition if you wish (see 11.2.1) but the default setting allows key duplication. Occasional repetitions may not be serious, but a field which can have only a few “values” is usually a poor choice. A customer number or membership number is the sort of thing we are looking for but your database may not contain anything like that. In a database of school pupils the pupil’s name would be a good choice of key, but the form teacher’s name would not, since only a small number of names would be involved, each appearing on the record of many pupils. Suppose you decide to use a person’s name, stored surname first, in a single field whose tag is NAME, as a key. You could use the whole name but it would probably be too long. The first four letters would be more appropriate but, as this is the start of the surname, you might get a lot of duplication. e.g. PRESTON and PRESCOTT would both have the key PRES; and this is before we even consider people whose surnames are identical. To get round this problem, Powerbase lets you construct an alphanumeric key from characters taken from up to four successive words. You will seldom need to go so far. In the present case a five-letter key made up from the first four letters of the surname and one letter of the forename would be good enough for most purposes. Set up the Key Structure window as follows: Field Word Position Chars 1st row: NAME 1 L 4 2nd row: NAME 2 L 1 This works quite well. Smith Peter and Smith Janet would have the keys SMITP and SMITJ. Duplication can still occur, but not often enough to be a serious problem. Further examples illustrate the use of letters from different parts of words in the key field. The following settings of word, position and characters would produce the keys shown from the name Herring Albert. (a)(d) use the Surname only, (e) and (f) use the Forename only, the rest use both names: From Surname From Forename Word Pos Chars Word Pos Chars Key (a) 1 L 5 - - - HERRI (b) 1 R 4 - - - RING (c) 1 2 3 - - - ERR (d) 1 4 6 - - - RING (stops at word end) (e) - - - 2 L 3 ALB (f) - - - 2 R 4 BERT (g) 1 L 4 2 L 4 HERRALBE (h) 2 L 3 1 R 3 ALBING (i) 1 4 3 2 4 2 RINER (j) 0 R 7 - - - GALBERT (ignores breaks) (k) - 4 6 - - - RINGAL (ignores breaks) Take particular note of what happens if the word number is entered as 0 (or left blank). All breaks between words are then ignored. The field is treated as if the blanks between words had been removed leaving a single long word which is then subjected to the process specified by the position and characters icons. When, on the other hand, the word number is 1 or greater the scanning for characters stops when the end of the word is reached so that the key might be shorter than the character length specified. Example (d) illustrates this. 4.5.3 Using more than one field in a key You might, of course, want to store surname and forename in different fields. Let’s call their tags SNAM and FNAM. To produce the five-letter keys given in our first example the key definition would be: - 34 - Field Word Position Chars 1st row: SNAM 1 L 4 2nd row: FNAM 1 L 1 and the keys generated will be exactly the same as they would be for the single field NAME. There are databases where no single field is suitable for constructing the primary key. In a database of classical music, for example, there would probably be one field for the composer’s name and one for the name of the work. Neither is much use individually; the former could contain many occurrences of Mozart or Beethoven and the latter many repetitions of Symphony No. 5 or String Quartet in D minor. This is the kind of situation where you might want to use two fields and as many as four words, taking one word from the Composer field and three from the Work field using, say, 4, 3, 3 and 2 characters respectively from them. The following two works then give the keys shown (characters taken from the left in each case): BEETHOVEN Symphony No 5: BEETSYMNO5 MOZART Piano concerto 23: MOZAPIACON23 Note the following points in these examples: (a) If a word is shorter than the number of letters assigned to it (“No” in the first example) then the whole word is used but no padding is inserted. (b) It may be necessary to omit an insignificant word (“No” in the second example), in order to make a significant one (“23”) come in the first four words of the combined key fields. NOTE It is not usually sensible to build up keys derived from Numeric fields out of separate bits in this way and, indeed, Powerbase won’t allow you to do so. Numeric keys should use one key-field only, the key being the actual numeric value of the field contents. 4.5.4 Other matters concerning keys We have already noted that a word shorter than the number of characters specified is used just as it is, resulting in a short key. There might be times when you want short words padding out with spaces to give a key of full length and there is a switch to select this action in the Key Structure window. A final factor to be decided is whether the indexing is to pay attention to the case of letters, i.e. if they are capitals or small letters By default indexing is not case-sensitive. Thus if a record has the word “Horse” as the contents of a key field and the first four letters are used as the key then the entry in the index will be “HORS” and you may search for it by entering “HORS”, “hors”, “Hors”, “hOrS” etc. This may not be what you want. If you require indexing to be case sensitive select the switch labelled Case. Forcing to upper case as described above does not then take place: keys are inserted and strings are searched for “as is”. The field containing “Horse” will be indexed as “Hors” and only that precise combination of upper and lower case letters will successfully find it. Alphanumeric keys will be ordered according to the ASCII values* of the letters. Since lower-case letters come later in the ASCII table than upper-case ones a record containing “dog” would appear after one containing “Horse”, whereas one containing “Dog” would come before “Horse”. 4.6 Building the empty database ----------------------------------- Once you are satisfied click Create and blank Database and PrimaryKey files will be created. The record window is redrawn and you may start entering data at once. 4.7 Renaming a database --------------------------- Use the Rename database option on the Miscellaneous submenu to do this. If you rename via the filer your database will lose its ‘PB’ icon and revert to the default application icon (‘APP’ with Archimedes ‘A’, or 2 large exclamation marks if you have RISC OS 4). Rename database is duplicated on the iconbar menu so that you can rename at the record-design stage. - 35 -